<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" xmlns:xlg="urn:xlg" xmlns:exsl="http://exslt.org/common">
	<xsl:output method="text" />
	<xsl:template match="/xlgDoc">
<!--
USE [<xsl:value-of select="@CacheDbName" />]
GO

-->
-- ----------------------------------------------------------------
-- CREATE NOT ALREADY EXISTING TABLES IN T-SQL
-- ----------------------------------------------------------------
	<xsl:for-each select="Tables/Table">
		<xsl:sort select="@TableName"/>
		<xsl:variable name="TableName" select="@TableName" />
		<xsl:variable name="ClassName" select="@ClassName" />

/****** Object:  Table [dbo].[<xsl:value-of select="$TableName" />] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[<xsl:value-of select="$TableName" />]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[<xsl:value-of select="$TableName" />](
	<xsl:for-each select="Columns/Column"><xsl:if test="position()!=1">,</xsl:if>
	[<xsl:value-of select="@ColumnName" />] <xsl:value-of select="@TSqlType" /><xsl:text> </xsl:text><xsl:if test="@IsNullable='False'">NOT </xsl:if>NULL <xsl:if test="@IsPrimaryKey='True'"> PRIMARY KEY CLUSTERED</xsl:if>
	</xsl:for-each>
	<xsl:variable name="IsClustered" select="@IsClustered" />
	<xsl:for-each select="Indexes/Index[@IsPrimary='True' and count(IndexColumns/IndexColumn) &gt; 1]">
	CONSTRAINT [PK_<xsl:value-of select="@IndexName" />] PRIMARY KEY <xsl:if test="$IsClustered!='True'">NON</xsl:if>CLUSTERED (
		<xsl:for-each select="IndexColumns/IndexColumn">
		<xsl:if test="position()!=1">,</xsl:if><xsl:value-of select="@IndexColumnName" />
		</xsl:for-each>
		) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
	</xsl:for-each>
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
	-- ----------------------------------------------------------------
	-- Defined Indexs
	-- ----------------------------------------------------------------
		<xsl:for-each select="Indexes/Index[@IsPrimary!='True']">
			<xsl:sort select="@IndexName"/>
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[<xsl:value-of select="$TableName" />]') AND name = N'<xsl:value-of select="$ClassName" />_<xsl:value-of select="@PropertyName" />')
	CREATE <xsl:if test="@IsUnique='True'">UNIQUE </xsl:if><xsl:if test="@IsClustered!='True'">NON</xsl:if>CLUSTERED INDEX [<xsl:value-of select="$ClassName" />_<xsl:value-of select="@PropertyName" />] ON [dbo].[<xsl:value-of select="$TableName" />] (
		<xsl:for-each select="IndexColumns/IndexColumn">
		<xsl:if test="position()!=1">,</xsl:if>[<xsl:value-of select="@IndexColumnName" />] ASC</xsl:for-each> 
	) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
		</xsl:for-each>

	</xsl:for-each>
SET ANSI_PADDING OFF
GO
-- ----------------------------------------------------------------
-- Additional Indexing SQL (maint_date)
-- ----------------------------------------------------------------
		<xsl:for-each select="Tables/Table[Columns/Column[starts-with(@ColumnName,'maint_date')]]">
		<xsl:sort select="@TableName"/>
		<xsl:variable name="TableName" select="@TableName" />
		<xsl:variable name="ClassName" select="@ClassName" />

-- ----------------------------------------------------------------
-- <xsl:value-of select="$TableName"/>
		<xsl:for-each select="Columns/Column[starts-with(@ColumnName,'maint_date')]">
			<xsl:sort select="@ColumnName"/>
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[<xsl:value-of select="$TableName" />]') AND name = N'<xsl:value-of select="$ClassName" />_<xsl:value-of select="@PropertyName" />')
   CREATE NONCLUSTERED INDEX [<xsl:value-of select="$ClassName" />_<xsl:value-of select="@PropertyName" />] ON [dbo].[<xsl:value-of select="$TableName" />] ( [<xsl:value-of select="@ColumnName" />] ASC ) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]</xsl:for-each>
GO
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>